from tkinter import *
from tkinter import filedialog
from tkinter import ttk
from threading import Thread
from queue import Queue
import openpyxl.workbook
from config import *
from sql import sqlseverDB
from mini_tools.cjjtool import *
from mini_tools.myexcel import *
from mini_tools.mytools import *
import tkinter.messagebox as messagebox
import openpyxl,time,concurrent.futures,json,os

class DataFra3(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        YNBBFrame(self).pack(fill=X,padx=2,pady=2)
        HuoYuanFrame(self).pack(fill=X,padx=2,pady=2)
        DesfyFrame(self).pack(fill=X,padx=2,pady=2)
        QinQuanFrame(self).pack(fill=X,padx=2,pady=2)

class YNBBFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        # self.pack(fill=X,padx=3,pady=3)
        self.rowCount=IntVar()

        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.cdnum1=IntVar()
        self.cdnum2=IntVar()
        Thread(target=self.getcount).start()
        self.createWidgets()
    
    def createWidgets(self):
        ttk.Separator(self,orient='horizontal').pack(fill=X,pady=5)
        
        Label(self, text='印尼虾皮自营店铺', fg="black", bg="yellow",font=("微软雅黑", 14)).pack(fill=X,padx=5)
        f0 = Frame(self)
        f0.pack(fill=X, padx=2, pady=2)
        f04=Frame(f0)
        f04.pack(fill=X)
        Label(f04, text='流量报表出单', fg="white", bg="#007FFF",font=("微软雅黑", 13)).pack(fill=X,padx=5)
        f00=Frame(f0)
        f00.pack(fill=X, padx=2, pady=2)
        Label(f00, text='导入虾皮流量报表:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f00)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f00, textvariable=self.rowCount,fg='red',width=4).pack(side=LEFT, padx=2)
        Button(f00,text='选择文件',fg='#ffffff',bg='green',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)
 
        self.btn1=Button(f00,text='一次自然出单',fg='#ffffff',bg='green',command=lambda:Thread(target=self.zrcd,args=(1,)).start())
        self.btn1.pack(side=LEFT,padx=6, pady=2)
        Label(f00, textvariable=self.cdnum1,fg='red',width=3).pack(side=LEFT, padx=2, pady=2)

        self.btn2=Button(f00,text='二次自然出单',fg='#ffffff',bg='green',command=lambda:Thread(target=self.zrcd,args=(2,)).start())
        self.btn2.pack(side=LEFT,padx=6, pady=2)
        Label(f00, textvariable=self.cdnum2,fg='red',width=3).pack(side=LEFT, padx=2, pady=2)

    def choosefile(self):
        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0,select_path)
            self.fpath=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.rowCount.set(max_row-1)
        else:
            messagebox.showerror('错误提示','请选择excel文件')
    
    def getcount(self):

        while True:
            try:
                rs1=self.ms.ExecQuery('select count(DISTINCT itemid) from proshopeeph where zlorder1=1')
                rs2=self.ms.ExecQuery('select count(DISTINCT itemid) from proshopeeph where zlorder2=1')
                self.cdnum1.set(rs1[0][0])
                self.cdnum2.set(rs2[0][0])
            except Exception as e:
                pass

    def zrcd(self,cdtype):
        
        if not self.DaoRuIpt.get().strip():
            messagebox.showinfo('提示','请先导入报表')
            return
        cdnames=['一次自然出单','二次自然出单','推广出单']
        cur_cdname=cdnames[cdtype-1]
        if not messagebox.askyesno('提示',f'确定提交{cur_cdname}'):
            return
        
        getattr(self, f'btn{cdtype}').config(state=DISABLED)
        #获取报表内容
        data_bb=self.get_bb(self.fpath)
        print(f'共获取 {len(data_bb.keys())} 个报表产品')


        print('正在获取数据库所有产品数据...')
        all_pros=self.get_dbpro()
        print(f'数据库共获取 {len(all_pros)} 个已铺货产品')

        print('开始报表产品和数据库产品进行匹配...')
        self.up_q=Queue(-1)
        for bb_itemid,bb_list in data_bb.items():
            bb_row=bb_list[0]
            bb_status=bb_list[3]
            bb_skuName=bb_list[4]
            bb_buyers=bb_list[5]
            bb_total_buyers=bb_list[6]
            for cur_dbpro in all_pros:
                db_itemid,ynitemid1,ynitemid2=cur_dbpro
                if cdtype==1:
                    if bb_itemid==ynitemid1:
                        self.up_q.put([bb_row,db_itemid,bb_status,cdtype,bb_skuName,bb_buyers,bb_total_buyers])
                elif cdtype==2:
                    if bb_itemid==ynitemid2:
                        self.up_q.put([bb_row,db_itemid,bb_status,cdtype,bb_skuName,bb_buyers,bb_total_buyers])

        print(f'{cur_cdname},共匹配到{self.up_q.qsize()}个产品')

        uptasks=[]
        for _ in range(5):
            t0=Thread(target=self.upproinfo)
            uptasks.append(t0)
            t0.start()
        
        for t in uptasks:
            t.join()
        
        print(f'{cur_cdname}操作完毕')
        messagebox.showinfo('提示',f'{cur_cdname}操作完毕')

        getattr(self, f'btn{cdtype}').config(state=NORMAL)
    
    def upproinfo(self):
        while True:
            if self.up_q.empty():
                print('更新队列为空')
                break

            bb_row,db_itemid,bb_status,cdtype,bb_skuName,bb_buyers,bb_total_buyers=self.up_q.get()
            cdnames=['一次自然出单','二次自然出单']
            cur_cdname=cdnames[cdtype-1]
            print(f'《{cur_cdname}》更新队列剩余:{self.up_q.qsize()}')

            if cdtype==1:
                sqlstr='update [proshopeeph] set [ynurState1]=?,[zlorder1]=1,buyers1=?,cdSku1=?,totalBuyers1=? where [itemid]=?'
            elif cdtype==2:
                sqlstr='update [proshopeeph] set [ynurState2]=?,[zlorder2]=1,buyers2=?,cdSku2=?,totalBuyers2=? where [itemid]=?'

            aff=self.ms.ExecNoQuery(sqlstr,(bb_status,bb_buyers,bb_skuName,bb_total_buyers,db_itemid)) 
            if aff>0:
                print(f'导入的报表第{bb_row} 行,《{cur_cdname}》成功更新到虾皮数据库产品表')
    

    def get_bb(self,cfpath):
        wb=openpyxl.load_workbook(cfpath)
        ws=wb[wb.sheetnames[0]]
        data_bb={}
        for r in range(2,ws.max_row+1):
            if ws.cell(row=r,column=18).value is None:
                break
            itemid=str(ws.cell(row=r,column=1).value.strip())
            title=ws.cell(row=r,column=2).value.strip()
            status=ws.cell(row=r,column=3).value.strip()
            skuName=ws.cell(row=r,column=5).value.strip()
            isorder=int(ws.cell(row=r,column=18).value.strip())
            buyerCount=str(ws.cell(row=r,column=22).value.strip())

            if isorder>=1 and itemid and skuName!='-':
                if not data_bb.get(itemid):
                    data_bb[itemid]=[r,itemid,title,status,skuName,buyerCount,int(buyerCount)]
                else:
                    cur_item=data_bb[itemid]
                    cur_item[4]+='|'+skuName
                    cur_item[5]+='|'+buyerCount
                    cur_item[6]+=int(buyerCount)

        
        return data_bb

    def get_dbpro(self):

        path=DBPROPATH
        all_pros=[]
        if os.path.exists(path) and time.time()-os.path.getmtime(path)<86400:
            with open(path,'rb') as f:
                all_pros=json.load(f)
            
            return all_pros
        else:
            rsdpp=self.ms.ExecQuery('''select itemid,ynitemid1,ynitemid2 from ProShopeePh where [id] in 
                                    (select MIN(id) from ProShopeePh 
                                    where ynitemid1 is not null 
                                    or ynitemid2 is not null
                                    GROUP BY itemid)''')

            for pr in rsdpp:
                itemid,ynitemid1,ynitemid2=pr
                all_pros.append([itemid,ynitemid1,ynitemid2])

            with open(path,'w',encoding='utf-8') as f:
                json.dump(all_pros,f,ensure_ascii=False)
            return all_pros
    
 
class HuoYuanFrame(Frame):

    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.countStr=StringVar()
        self.countStr.set('正在获取需导出数量...')
        Thread(target=self.getcount).start()
        self.createWidgets()

    def createWidgets(self):

        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        Label(f0, text='查找1688货源数据', fg="white", bg="#007FFF",font=("微软雅黑", 13)).pack(padx=2,pady=2,fill=X)

        f1=Frame(self)
        f1.pack(side=LEFT,padx=2,pady=2)

        f10=Frame(f1)
        f10.pack(fill=X,padx=2,pady=2)
        Label(f10, textvariable=self.countStr,fg='red',width=22).pack(side=LEFT,padx=2,pady=2)
        self.btndc=Button(f10,text='导出需查找1688货源数据',command=lambda:Thread(target=self.dcxcz).start(),fg='#ffffff',bg='green')
        self.btndc.pack(side=LEFT,padx=2,pady=2)
        f11=Frame(f1)
        f11.pack(fill=X,padx=2,pady=2)
        Label(f11, text='ItemID:').pack(side=LEFT, padx=2, pady=2)
        self.itemidIpt = Entry(f11)
        self.itemidIpt.pack(side=LEFT, padx=2, pady=2)
        self.btnss=Button(f11,text='搜索当前itemid产品',command=lambda:Thread(target=self.dcss).start(),fg='#ffffff',bg='green')
        self.btnss.pack(side=LEFT,padx=2,pady=2)

        f2=Frame(self)
        f2.pack(side=LEFT,padx=35,pady=2)
        HY2Frame(f2).pack()


    
    def getcount(self):
        sqlstr='''select count(*) from ProShopeePh where id in(select MIN(id) from ProShopeePh where itemid in 
        (select DISTINCT itemid from ProShopeePh where zlorder1=1 and ishy=0) group by itemid)'''
        while True:
            try:
                rs=self.ms.ExecQuery(sqlstr)
                self.countStr.set(f'需查找货源的产品共 {rs[0][0]} 个')
                time.sleep(10)
            except Exception as e:
                print(e)

    def dcxcz(self):
        self.btndc.config(state=DISABLED)
        print('正在连接数据库...')
        sqlstr='''
                select
                [url] as 产品链接,
                ynurl1 as 印尼虾皮一次铺货链接,
                dpname1 as 印尼一次铺货店铺名称,
                uname1 as 印尼一次铺货店铺登录用户名,
                cpbz as 产品备注,
                aliurl as [1688采购链接],
                aliurlsx1 as [1688采购链接属性1],
                aliurlsx2 as [1688采购链接属性2],
                aliurlsx3 as [1688采购链接属性3],
                cgmaxprice as [1688最贵SKU采购价格],
                ishy as 是否有货源 from ProShopeePh
                where id in(select MIN(id) from ProShopeePh where itemid in 
                (select DISTINCT itemid from ProShopeePh where zlorder1=1 and ishy=0) group by itemid)
                order by dpname1
                '''
        rs=self.ms.ExecQuerydict(sqlstr)
        self.create_excel(rs)
        self.btndc.config(state=NORMAL)
    
    def dcss(self):
        cpidstr=self.itemidIpt.get().strip()
        if not cpidstr:
            messagebox.showinfo('提示','请输入产品ItemId或者产品链接')
            return

        self.btnss.config(state=DISABLED)

        if not cpidstr.isdigit():
            cpidstr=get_pid_byurl(cpidstr)[1]
        
        sqlstr='''
                select 
                ynurl1 as 印尼虾皮一次铺货链接,
                cpbz as 产品备注,
                aliurl as [1688采购链接],
                aliurlsx1 as [1688采购链接属性1],
                aliurlsx2 as [1688采购链接属性2],
                aliurlsx3 as [1688采购链接属性3],
                cgmaxprice as [1688最贵SKU采购价格],
                ishy as 是否有货源 from ProShopeePh
                where id in(select MIN(id) from ProShopeePh where itemid in 
                (select DISTINCT itemid from ProShopeePh where ynitemid1=? or ynitemid2=? or ynitemid3=?) group by itemid)
                '''
        rs=self.ms.ExecQuerydict(sqlstr,(cpidstr,cpidstr,cpidstr))
        if len(rs)==0:
            print(f'没查询到ItemId为:{cpidstr} 的产品')
            messagebox.showinfo('提示',f'没查询到ItemId为:{cpidstr} 的产品')
        else:
            self.create_excel(rs)
        self.btnss.config(state=NORMAL)

    def create_excel(self,rs):

        wb=openpyxl.Workbook()
        ws=wb[wb.sheetnames[0]]
        ws.append(list(rs[0].keys()))
        for r in rs:
            ws.append(list(r.values()))
        current_time_str = time.strftime("%Y-%m-%d_%H-%M-%S", time.localtime(time.time()))
        fname=f'需找货源_{current_time_str}.xlsx'
        wb.save(fname)
        print(f'成功导出 {ws.max_row-1} 行需找货源产品')
        messagebox.showinfo('提示',f'成功导出 {ws.max_row-1} 行需找货源产品到文件 {fname}')

class HY2Frame(Frame):

    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.countStr=StringVar()
        self.countStr.set('当前未导入表格')
        self.createWidgets()

    def createWidgets(self):

        f0 = Frame(self)
        f0.pack(fill=X, padx=2, pady=2)
        f00=Frame(f0)
        f00.pack(fill=X, padx=2, pady=2)
        Label(f00, text='导入货源表:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f00)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Button(f00,text='选择文件',fg='#ffffff',bg='green',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)
        Label(f00, textvariable=self.countStr,fg='red').pack(side=LEFT, padx=2, pady=2)

        f01=Frame(f0)
        f01.pack(fill=X, padx=2, pady=2)  
        self.btndr=Button(f01,text='导回数据库',command=lambda:Thread(target=self.dhhh).start(),fg='#ffffff',bg='green')
        self.btndr.pack(side=LEFT,padx=2,pady=2)

    def choosefile(self):

        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0,select_path)
            self.fpath=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.countStr.set(f'当前表格共{max_row-1}行')
        else:
            messagebox.showerror('错误提示','请选择excel文件')
    
    def dhhh(self):

        if not self.DaoRuIpt.get().strip():
            messagebox.showinfo('提示','请先导入文件')
            return
        
        self.btndr.config(state=DISABLED)
        wb = openpyxl.load_workbook(self.fpath)
        ws=wb[wb.sheetnames[0]]
        exdatas=getDictDatasByEecel(ws,0)
        ws.delete_rows(2, ws.max_row-1)
        row=2
        for exline in exdatas:
            try:
                ynurl1=exline['印尼虾皮一次铺货链接']
                cpbz=exline['产品备注']
                cgurl=exline['1688采购链接']
                cgurl=exline['1688采购链接']
                urlsx1=exline['1688采购链接属性1']
                urlsx2=exline['1688采购链接属性2']
                urlsx3=exline['1688采购链接属性3']
                ishy=int(exline['是否有货源'])
                if ishy!=1 and ishy!=-1:
                    print(f'第 {row} 行,是否有货源填写错误')
                    ws.append(list(exline.values()))
                    row+=1
                    continue
                if ishy==1:
                    maxskuprice=float(exline['1688最贵SKU采购价格'])
                else:
                    maxskuprice=None

                yn_itemid=get_pid_byurl(ynurl1)[1]            
                sqlstr='''update ProShopeePh set cpbz=?,aliurl=?,aliurlsx1=?,aliurlsx2=?,aliurlsx3=?,cgmaxprice=?,
                        ishy=? where ynItemid1=?
                        '''
                params=[cpbz,cgurl,urlsx1,urlsx2,urlsx3,maxskuprice,ishy,yn_itemid]
                aff=self.ms.ExecNoQuery(sqlstr,params)
                if aff>0:
                    print(f'第 {row} 行,YN_itemid:{yn_itemid},更新成功,成功更新 {aff} 个sku')
                else:
                    print(f'第 {row} 行,YN_itemid:{yn_itemid},更新失败')
                    ws.append(list(exline.values()))
            
            except Exception as e:
                print(f'表格第 {row} 行,导入出错 => {e}')
                ws.append(list(exline.values()))
            row+=1
        wb.save(self.fpath)
        print('导入完毕')
        messagebox.showinfo('提示','导入完毕')

        self.btndr.config(state=NORMAL)

class DesfyFrame(Frame):

    def __init__(self,master=None):
        Frame.__init__(self, master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.statustxt2=StringVar()
        self.statustxt2.set(f'未导入已翻译表')
        self.createWidgets()

    def createWidgets(self):

        Label(self, text='标题-描述翻译', fg="white", bg="#007FFF",font=("微软雅黑", 13),width=50).pack(fill=X,padx=5)
        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2,side=LEFT)
        BtfyFrame(f1).pack()
        self.statustxt=StringVar()
        self.statustxt.set('正在获取数据...')
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2,side=RIGHT)
        f03=Frame(f0)
        f03.pack(pady=2,fill=X,padx=2)
        Label(f03, textvariable=self.statustxt,fg='red').pack(side=LEFT, padx=2, pady=2)
        Thread(target=self.getkwcount).start()

        f00=Frame(f0)
        f00.pack(pady=2,fill=X,padx=2)

        self.btn_dc=Button(f00,text='导出原始描述',fg='#ffffff',bg='green',command=lambda:Thread(target=self.dc_des).start())
        self.btn_dc.pack(side=LEFT, padx=2, pady=2)

        f01=Frame(f0)
        f01.pack(pady=5,fill=X,padx=5)
        Label(f01, text='导入翻译表:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f01)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Button(f01,text='选择文件',fg='#ffffff',bg='green',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)
        
        f02=Frame(f0)
        f02.pack(fill=X,pady=2,padx=2)
        self.btn_dr=Button(f02,text='上传印尼描述',fg='#ffffff',bg='green',command=lambda:Thread(target=self.dr_yndes).start())
        self.btn_dr.pack(side=LEFT, padx=2, pady=2)
        Label(f02, textvariable=self.statustxt2,fg='red').pack(side=LEFT, padx=2, pady=2)
    
    def getkwcount(self):
        while True:
            try:
                
                rs=self.ms.ExecQuery('''SELECT COUNT(DISTINCT ynitemid1) from ProShopeePh where newmaxprice is not null''')
                
                rs1=self.ms.ExecQuery('''SELECT COUNT(DISTINCT ynitemid1) from ProShopeePh where newmaxprice is not null and yndes is not null''')
                
                self.statustxt.set(f'需翻译描述共 {rs[0][0]} 个,其中已翻译 {rs1[0][0]} 个,未翻译 {rs[0][0]-rs1[0][0]} 个')
                time.sleep(10)
            except Exception as e:
                continue

    def dc_des(self):
        
        if not messagebox.askyesno('提示','是否导出未翻译描述?'):
            return
        
        self.btn_dc.config(state=DISABLED)
        print('正在链接数据库...')
        rp=self.ms.ExecQuery(f'''select ynitemid1,ynurl1,pdec from ProShopeePh where [id] in (select MIN(id) from ProShopeePh where
                                newmaxprice is not null and yndes is null GROUP BY ynitemid1) order by [id]''')

        
        wb=openpyxl.Workbook()
        ws=wb[wb.sheetnames[0]]
        ws.append(['印尼ItemID','印尼虾皮一次铺货链接','产品描述','印尼描述'])
        row=0
        for ynitemid,ynurl1,pdes in rp:

            try:
                ws.append([ynitemid,ynurl1,pdes,''])
                row+=1
            except Exception as e:

                print(f'第 {row} 行,导出错误 => {e}')

        current_time_str = time.strftime("%Y-%m-%d_%H-%M-%S", time.localtime(time.time()))
        cur_fname=f'原始描述_{current_time_str}.xlsx'
        wb.save(cur_fname)
        print(f'本次导出描述完毕,共导出{row}行')
        messagebox.showinfo('提示',f'本次导出描述完毕,共导出{row}行')
        self.btn_dc.config(state=NORMAL)

    def choosefile(self):
        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0,select_path)
            self.fpath=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.statustxt2.set(f'当前翻译表共{max_row-1}行')
        else:
            messagebox.showerror('错误提示','请选择excel文件')
    
    def dr_yndes(self):

        if not self.DaoRuIpt.get() :
            messagebox.showinfo('提示','请先导入翻译表')
            return
        self.btn_dr.config(state=DISABLED)
        wb=openpyxl.load_workbook(self.fpath)
        ws=wb[wb.sheetnames[0]]
        tsb=getDictDatasByEecel(ws,0)
        ws.delete_rows(2,ws.max_row-1)
        self.ws=ws
        self.cpcount=0
        with concurrent.futures.ThreadPoolExecutor(max_workers=10) as executor:
            # 提交任务给线程池
            cr=2
            for item in tsb:
                if item['印尼描述']:
                    executor.submit(self.uptask, [item['印尼ItemID'],item['印尼描述'],item['产品描述'],cr])
                else:
                    self.ws.append(list(item.values()))
                cr+=1
                

        executor.shutdown(wait=True)

        print(f'全部上传完毕,共上传 {self.cpcount} 行')
        messagebox.showinfo('提示',f'全部上传完毕,共上传 {self.cpcount} 行')
        wb.save(self.fpath)
        self.btn_dr.config(state=NORMAL)

    def uptask(self,item):
        yn_itemid,yndes,pdes,cr=item
        try:
            aff=self.ms.ExecNoQuery('update ProShopeePh set yndes=? where ynitemid1=?',(yndes,yn_itemid))
            
            if aff>0:
                self.cpcount+=1
                print(f'第{cr}行,yn_itemid:{yn_itemid},上传成功,当前共上传: {self.cpcount} 行')
            else:
                print(f'第{cr}行,yn_itemid:{yn_itemid},上传失败')
                self.ws.append([yn_itemid,'','',yndes])

        except Exception as e:
            print(f'第{cr}行,yn_itemid:{yn_itemid},上传出错 => e')
            self.ws.append([yn_itemid,'',pdes,yndes])

class BtfyFrame(Frame):

    def __init__(self,master=None):
        Frame.__init__(self, master)

        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.statustxt2=StringVar()
        self.statustxt2.set(f'当前未导入翻译表')
        self.createWidgets()

    def createWidgets(self):

        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        self.statustxt=StringVar()
        self.statustxt.set('正在获取数据...')
        Label(f1, textvariable=self.statustxt,fg='red').pack(side=LEFT, padx=2, pady=2)
        Thread(target=self.getkwcount).start()
        f00=Frame(f0)
        f00.pack(pady=2,fill=X,padx=2)
        Label(f00, text='最大字符数:').pack(side=LEFT, padx=2, pady=2)
        self.WordNumIpt = Entry(f00,width=10)
        self.WordNumIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f00,text='导出个数:').pack(side=LEFT, padx=2, pady=2)
        self.fileNumIpt = Entry(f00,width=10)
        self.fileNumIpt.pack(side=LEFT, padx=2, pady=2)
        self.btn_dc=Button(f00,text='导出原始标题',fg='#ffffff',bg='green',command=lambda:Thread(target=self.dc_title).start())
        self.btn_dc.pack(side=LEFT, padx=2, pady=2)

        f01=Frame(f0)
        f01.pack(pady=2,fill=X,padx=2)
        Label(f01, text='导入翻译表:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f01)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Button(f01,text='选择文件',fg='#ffffff',bg='green',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)

        f02=Frame(f0)
        f02.pack(fill=X,padx=2)
        self.btn_dr=Button(f02,text='上传印尼标题',fg='#ffffff',bg='green',command=lambda:Thread(target=self.dr_yntitle).start())
        self.btn_dr.pack(side=LEFT, padx=2, pady=2)
        Label(f02, textvariable=self.statustxt2,fg='red').pack(side=LEFT, padx=2, pady=2)
    
    def getkwcount(self):
        while True:
            try:
                rs=self.ms.ExecQuery('''SELECT COUNT(DISTINCT itemid) from ProShopeePh where daochu=0''')
                
                rs1=self.ms.ExecQuery('''SELECT COUNT(DISTINCT itemid) from ProShopeePh where daochu=0 and yntitle is not null''')
                
                self.statustxt.set(f'需翻译标题共 {rs[0][0]} 个,其中已翻译 {rs1[0][0]} 个,未翻译 {rs[0][0]-rs1[0][0]} 个')
                time.sleep(10)
            except Exception as e:
                continue


    def dc_title(self):
        
        if not self.WordNumIpt.get() or not self.fileNumIpt.get():
            messagebox.showinfo('提示','请先填写字符限制和文件数量')
            return

        self.btn_dc.config(state=DISABLED)
        wordnum=int(self.WordNumIpt.get())
        filenum=int(self.fileNumIpt.get())
        print(f'每份文件最大字符数:{wordnum},导出文件个数:{filenum}')
        rp=self.ms.ExecQuery(f'''select itemid,pname from ProShopeePh where [id] in (select MIN(id) from ProShopeePh where
                                daochu=0 and yntitle is null GROUP BY itemid) ''')
        current_timestamp = time.time()
        current_time_str = time.strftime("%Y-%m-%d_%H-%M-%S", time.localtime(current_timestamp))
        dcmr=f'原始标题{current_time_str}'
        os.mkdir(dcmr)
        wordcount=0
        filecount=0
        row=0
        wb=openpyxl.Workbook()
        ws=wb[wb.sheetnames[0]]
        ws.append(['PID','标题','印尼的标题'])
        for itemid,title in rp:
            try:
                if filecount>=filenum:
                    print(f'已经成功导出{filecount}份文件')
                    break
                if (wordcount+len(title))>=wordnum:
                    wb.save(f'{dcmr}/原始标题{filecount+1}.xlsx')
                    print(f'已导出{filecount+1}.xlsx')
                    wb=openpyxl.Workbook()
                    ws=wb[wb.sheetnames[0]]
                    ws.append(['PID','标题','印尼的标题'])
                    wordcount=0
                    filecount+=1
                    
                ws.append([itemid,title,''])
                wordcount+=len(title)
            except Exception as e:
                print(f'第{row}行,出错=>{e}')
            row+=1

        print(f'本次导出标题完毕,共导出{row}行')
        messagebox.showinfo('提示',f'本次导出标题完毕,共导出{row}行')
        self.btn_dc.config(state=NORMAL)
    def choosefile(self):
        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0,select_path)
            self.fpath=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.statustxt2.set(f'当前翻译表共{max_row-1}行')
        else:
            messagebox.showerror('错误提示','请选择excel文件')
    
    def dr_yntitle(self):

        if not self.DaoRuIpt.get() :
            messagebox.showinfo('提示','请先导入翻译表')
            return
        self.btn_dr.config(state=DISABLED)

        wb = openpyxl.load_workbook(self.fpath)
        ws=wb[wb.sheetnames[0]]
        tsb=getDictDatasByEecel(ws,0)
        self.cpcount=0
        with concurrent.futures.ThreadPoolExecutor(max_workers=10) as executor:
            # 提交任务给线程池

            for item in tsb:
                executor.submit(self.uptask, [item['PID'],item['印尼的标题']])
        executor.shutdown(wait=True)
        print(f'全部上传完毕,共上传 {self.cpcount} 行')
        messagebox.showinfo('提示',f'全部上传完毕,共上传 {self.cpcount} 行')
        self.btn_dr.config(state=NORMAL)

    def uptask(self,item):
        itemid,yntitle=item

        aff=self.ms.ExecNoQuery('update ProShopeePh set yntitle=? where itemid=?',(yntitle,itemid))
        
        if aff>0:
            self.cpcount+=1
            print(f'已上传{self.cpcount}行')

class QinQuanFrame(Frame):

    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.countStr=StringVar()
        self.countStr.set('正在获取需查侵权数量...')
        self.statustxt=StringVar()
        self.statustxt.set('当前未导入已查侵权表')
        Thread(target=self.getcount).start()
        self.createWidgets()

    def createWidgets(self):

        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        Label(f0, text='检查侵权', fg="white", bg="#007FFF",font=("微软雅黑", 13)).pack(padx=2,pady=2,fill=X)

        f01=Frame(f0)
        f01.pack(side=LEFT,fill=X,padx=2,pady=2)
        Label(f01, textvariable=self.countStr,fg='red',width=20).pack(side=LEFT,padx=2,pady=2)
        self.btnDcqq=Button(f01,text='导出需检查侵权',fg='#ffffff',bg='green',command=lambda:Thread(target=self.dcqq).start())
        self.btnDcqq.pack(side=LEFT,padx=2,pady=2)

        f02=Frame(f0)
        f02.pack(side=RIGHT,fill=X,padx=2,pady=2)
        f020=Frame(f02)
        f020.pack(fill=X)
        Label(f020, text='导入侵权表:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f020)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Button(f020,text='选择文件',fg='#ffffff',bg='green',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)
        Label(f020, textvariable=self.statustxt,fg='red',width=20).pack(side=LEFT,padx=2,pady=2)
        f021=Frame(f02)
        f021.pack(fill=X)
        self.btnDrqq=Button(f021,text='上传已查侵权',fg='#ffffff',bg='green',command=lambda:Thread(target=self.drqq).start())
        self.btnDrqq.pack(side=LEFT,padx=2,pady=2)


    
    def getcount(self):
            
        sqlstr='''
            SELECT count(DISTINCT p1.itemid)
            FROM proshopeeph p1
            where (p1.zlorder2=1 and p1.isqq=0)
        '''
        while True:
            try:
                rs=self.ms.ExecQuery(sqlstr)
                self.countStr.set(f'需检查侵权产品共 {rs[0][0]} 个')
                time.sleep(5)

            except Exception as e:
                print(e)
         

    def dcqq(self):

        self.btnDcqq.config(state=DISABLED)
        sqlstr='''
            select
            ynurl1 as [印尼虾皮一次铺货链接],
            ynurl2 as [印尼虾皮二次铺货链接],
            aliurl as [1688采购链接]
            from ProShopeePh where id in(
            SELECT min(id)
            FROM proshopeeph p1

                where (p1.zlorder2=1 and p1.isqq=0) 
            group by p1.itemid)
        '''
        rs=ms.ExecQuerydict(sqlstr)
        if len(rs)>0:
            wb=openpyxl.Workbook()
            ws=wb[wb.sheetnames[0]]
            ws.append(['印尼虾皮一次铺货链接','印尼虾皮二次铺货链接','1688采购链接','是否侵权(填1（不侵权）或-1（侵权）'])
            for r in rs:
                ynurl1=r['印尼虾皮一次铺货链接']
                ynurl2=r['印尼虾皮二次铺货链接']
                aliurl=r['1688采购链接']
                ws.append([ynurl1,ynurl2,aliurl,''])
            
            current_time_str = time.strftime("%Y-%m-%d_%H-%M-%S", time.localtime(time.time()))
            fname=f'需检查侵权_{current_time_str}.xlsx'
            wb.save(fname)
            print(f'成功导出{fname}')
            messagebox.showinfo('提示',f'成功导出《{fname}》')
        
        else:
            messagebox.showinfo('提示','暂时没有需检查侵权产品')

        self.btnDcqq.config(state=NORMAL)
    
    def choosefile(self):
        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0,select_path)
            self.fpath=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.statustxt.set(f'当前已查侵权表共{max_row-1}行')
        else:
            messagebox.showerror('错误提示','请选择excel文件')

    def drqq(self):

        if not self.DaoRuIpt.get().strip():
            messagebox.showinfo('提示','请先导入已查侵权表')
            return
        self.btnDrqq.config(state=DISABLED)
        datas=getDictDatasByEecel(delempty2(self.fpath),0)

        row=2

        with concurrent.futures.ThreadPoolExecutor(max_workers=5) as executor:
            futures=[]
            for r in datas:
                ynurl1,ynurl2,alurl,isqq=r.values()
                if isqq and str(isqq).isdigit() and int(isqq) in [1,-1]:

                    ynitemid1=get_pid_byurl(ynurl1)[1]
                    futures.append(executor.submit(self.updateQq,row,ynitemid1, int(isqq)))
                else:
                    print(f'第 {row} 行 ,侵权信息填写错误')
                row+=1
            
            for future in concurrent.futures.as_completed(futures):
                try:
                    res_row,res_ynitemid1,res_aff = future.result()
                    print(f'第{res_row}行,ynotemid1:{res_ynitemid1},更新sku数:{res_aff}')
                except Exception as exc:
                    print(f"线程出错: {exc}")
        print('侵权信息上传完毕')
        messagebox.showinfo('提示','侵权信息上传完毕')

        self.btnDrqq.config(state=NORMAL)
    
    def updateQq(self,row,ynitemid1,isqq):
        
        sqlstr='''update proshopeeph set isqq=? where ynitemid1=?'''
        aff=self.ms.ExecNoQuery(sqlstr,[isqq,ynitemid1])
        return (row,ynitemid1,aff)

# import tkinter
# list_win=tkinter.Tk()
# list_win.wm_geometry("800x650")
# list_win.title('ximing')
# DataFra3(list_win).pack(fill=X,padx=3,pady=3)
# list_win.mainloop()